package com.zsrt.oa.hobby.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zsrt.common.entity.Combobox;
import com.zsrt.oa.hobby.dao.HobbiesDao;
import com.zsrt.oa.hobby.entity.HobbiesEntity;
import com.zsrt.utils.DBUtils;

public class HobbiesDaoImpl implements HobbiesDao {
	
	/**
	 * 员工爱好增加
	 * @param entity
	 */
	@Override
	public void add(HobbiesEntity entity) throws SQLException{
		Connection con = DBUtils.getMessage();
		PreparedStatement ps = null;
		String[] hobbysId = entity.getHobbysId();
		String hobSql = "insert into OA_EMPLOYEE_HOBBY_T values(dd_s.nextval,?,?)";
		ps = con.prepareStatement(hobSql);
		for(int index=0; index<hobbysId.length; index++){
			ps.setInt(1, entity.getEmpId());
			ps.setString(2, hobbysId[index]);
			ps.addBatch();
		}
		ps.executeBatch();
		
		DBUtils.closeResources(con, ps);
	}
	
	/**
	 * 爱好动态生成
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<Combobox> empHobby() throws SQLException {
		List<Combobox> list = new ArrayList<Combobox>();
		
		Connection con = DBUtils.getMessage();
		String sql = "select id as hobbyId,children_name as hobbyName from oa_dd_detail_t where parent_id = '3001'";
		PreparedStatement ps = con.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Combobox box = new Combobox();
			box.setValueField(rs.getString("hobbyId"));
			box.setTextField(rs.getString("hobbyName"));
			list.add(box);
		}
		DBUtils.closeResources(con, ps ,rs);
		return list;
	}
	
	/**
	 * 员工爱好批量删除
	 * @param entity
	 * @throws SQLException
	 */
	@Override
	public void hobbiesDeleteByIds(HobbiesEntity entity) throws SQLException {
		Connection con = DBUtils.getMessage();
		PreparedStatement ps = null;
		
		String sql = "delete from oa_employee_hobby_t e where e.hobby_id = "
				+ "(select id from oa_dd_detail_t where parent_id = 3001 and e.hobby_id = id) "
				+ "and e.emp_id = ?";
		ps = con.prepareStatement(sql);
		String[] empIdArrays = entity.getHobbysId();
		for(int index=0; index<empIdArrays.length; index++){
			ps.setString(1, empIdArrays[index]);
			ps.addBatch();
		}
		ps.executeBatch();
		
		DBUtils.closeResources(con, ps );
	}
	
	/**
	 * 指定查询员工爱好
	 * @param empId
	 */
	public List<Combobox> hobbiesFindById(String empId) throws SQLException{
		List<Combobox> list = new ArrayList<Combobox>();
		Connection con = DBUtils.getMessage();
		String sql = "select dd.id as valueField,dd.children_name as textField "
				+ " from oa_employee_hobby_t e"
				+ " inner join oa_dd_detail_t dd"
				+ " on e.hobby_id = dd.id"
				+ " where e.emp_id = ?"
				+ " and dd.parent_id = 3001";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, empId);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Combobox box = new Combobox();
			box.setValueField(rs.getString("valueField"));
			box.setTextField(rs.getString("textField"));
			list.add(box);
		}
		
		DBUtils.closeResources(con, ps ,rs);
		return list;
	}
}
